package com.glomozda.machinerepairdao.repository.repairtype;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.glomozda.machinerepairdao.domain.repairtype.RepairType;

import static com.glomozda.machinerepairdao.repository.jdbc.DAOJDBCUtil.*;

@SuppressWarnings({"PMD.CommentRequired", "PMD.LawOfDemeter"})
@Repository("repairTypeRepository")
public class RepairTypeRepositoryJDBC implements RepairTypeRepository {
	
	private final transient DataSource ds;

    @Autowired    
    public RepairTypeRepositoryJDBC(final DataSource ds) {
        this.ds = ds;
    }
    
    @Override
    public RepairType find(final Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        RepairType repairType = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement
            		("select * from Repair_Types where repair_types_id=?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	repairType = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return repairType;
    }

    @Override
    public List<RepairType> findAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        final List<RepairType> repairTypes = new ArrayList<RepairType>();

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Repair_Types");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
            	repairTypes.add(map(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return repairTypes;
    }

    @Override
    public boolean create(final RepairType repairType) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int repairTypesQuantity = 0;
        
        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select count(*) from Repair_Types");
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	repairTypesQuantity = resultSet.getInt(1);
            }
            close(preparedStatement);
            
            preparedStatement = connection.prepareStatement("INSERT INTO Repair_Types "
                    + "(repair_types_id, name, price, duration) VALUES (?, ?, ?, ?)");
            preparedStatement.setInt(1, repairTypesQuantity + 1);
            preparedStatement.setString(2, repairType.getRepairTypeName());
            preparedStatement.setInt(3, repairType.getRepairTypePrice());
            preparedStatement.setInt(4, repairType.getRepairTypeDuration());            
            preparedStatement.execute();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            close(preparedStatement);
            close(connection);
        }
        return false;
    }

    private RepairType map(final ResultSet resultSet) throws SQLException {
    	final RepairType repairType = new RepairType();
    	repairType.setRepairTypeID(resultSet.getInt(1));
    	repairType.setRepairTypeName(resultSet.getString(2));
    	repairType.setRepairTypePrice(resultSet.getInt(3));
    	repairType.setRepairTypeDuration(resultSet.getInt(4));
        return repairType;
    }

    @Override
    public RepairType findByName(final String repairTypeName) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        RepairType repairType = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.
                    prepareStatement("select * from Repair_Types where name = ?");
            preparedStatement.setString(1, repairTypeName);
            resultSet = preparedStatement.executeQuery();
                      
            if (resultSet.next()) {
            	repairType = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return repairType;
    }
}
